/*

If alert received execute this process

1. Check the Alert tracking tracking table. 

drop table ReplicationLatencyTracking
drop table ReplicationLatencyHistory

*/

--create table ReplicationLatencyTracking (AlertTime datetime2 not null
--										,XtiviaNotifcation  char(1) null
--										,LoanmartNotification char(1) null
--										,AllClearNotification char(1) null)
										
--create table ReplicationLatencyHistory (AlertTime datetime2 not null
--                                         ,XtiviaNotifcation  char(1) null
--										,LoanmartNotification char(1) null
--											,AllClearNotification char(1) null)

-- On Alert trigger -Write to the historylog and check if notification needs to be sent
Declare @AlertDate datetime2
Declare @messageSent int
Declare @AllClear char(1)


Select @AlertDate = GETDATE()



-- Figure out if to send notification to Loanmart and VDBA
Declare @LastNotifcaitonSentDT datetime2

select @LastNotifcaitonSentDT = MAX(Alerttime) from ReplicationLatencyTracking

-- If no record in the ReplicationLatencyTracking Table insert a record
-- This should only be executed the first time an alert for latency happens
if @LastNotifcaitonSentDT is null
begin 
insert into ReplicationLatencyTracking (AlertTime, allclearnotification)
values (@Alertdate,'N')

set @LastNotifcaitonSentDT = dateadd(dd,-1,@AlertDate)
End


select @AllClear = Allclearnotification from replicationlatencyTracking

if (@AllClear = 'N' and @LastNotifcaitonSentDT < DATEADD(mi,-5,getdate()))
begin
  Print 'Execute the all clear job'
end  



-- Check to see if message needs to be sent to VDBA and Loanmart 
-- Current requirement is if an alert has not happened in the last hour

--select DATEDIFF(mI,@LastNotifcaitonSentDT,@alertDate)

If DATEDIFF(mi,@LastNotifcaitonSentDT,@alertDate) > 60
Begin
  Print 'Sent new notification and execute job to alert VDBA'
  -- Execute a DatabaseMail step to Loanmart Operators
declare @sub varchar(256)
set @sub = 'Replication Latency on  ' + @@SERVERNAME

exec msdb.dbo.sp_send_dbmail
@recipients= 'alerts@800loanmart.com, ittxtalerts@800loanmart.com,appdev@800loanmart.com',
@profile_name ='smtp01.loanmart.com',
@subject = @sub,
@body = 'There is replication latency occuring'


  -- Execute the SQL Server job to create file to VDBA. 
  exec msdb.dbo.sp_start_job N'VDBA - Replication Warning: Transactional Replication Latency'
  -- Flag for updating Sent to VDBA and Loanmart
  -- Start Job for the all clear notification. 
  
 set @messageSent = 1 
  end
  
 If @MessageSent = 1
 Begin
  Update ReplicationLatencyTracking
    set AlertTime = @AlertDate
    , LoanmartNotification = 'Y'
    , XtiviaNotifcation = 'Y'
    ,AllClearNotification = 'N'
    
   Insert into ReplicationLatencyHistory (AlertTime,LoanmartNotification,XtiviaNotifcation, allclearnotification)
   values (@AlertDate, 'Y','Y','N') 
    
 end
 else 
 Begin
  Update ReplicationLatencyTracking
    set AlertTime = @AlertDate
    , LoanmartNotification = 'N'
    , XtiviaNotifcation = 'N'
    , AllClearNotification = 'N'
    
        
   Insert into ReplicationLatencyHistory (AlertTime,LoanmartNotification,XtiviaNotifcation,allclearnotification)
   values (@AlertDate, 'N','N','N')
    
 End 


-- Cleanup the history table to only have 6 months of Data
delete from ReplicationLatencyHistory
where AlertTime < DATEADD(mm,-6,getdate())
  
  
  

										